const express = require("express");
const router = express.Router();
const path = require("path");
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const utilsServer = require("../utils/serverInfo.js");
const moment = require("moment-timezone");
const multer = require("multer");
const AdmZip = require("adm-zip");
const AppInfoParser = require("app-info-parser");
const plist = require("plist");
const fs = require("fs");
const { readManifest } = require("node-aab-parser");
const msgUtils = require("../utils/feishu.js");
const crypto = require("crypto");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");
const { exec } = require("child_process");
const config = require("../config");
const axios = require("axios");
const jwt = require("jsonwebtoken");
const jwksClient = require('jwks-rsa');
const poolmomo = require("../poolmomo.js");



//获取包名列表
router.post("/getAppVersionPkList", async (req, res) => {
    let user = await utils.getUserRole(req, res);
    let roleList = utils.filterAppVersionListByRoleKey(user.roleKey);
    if (user.roleAdmin || user.hasManage) {
        // 是管理 无条件  查询所有
        roleList = [];
    }
    let obj = req.body;
    let { page, size } = utils.pageSize(obj.page, obj.size);

    let sqlParams = [];

    let sql = `SELECT package_name FROM app_version_management`;

    if (roleList.length > 0) {
        // 添加WHERE子句以仅返回roleList中的包名
        let pkPlaceholders = roleList.map(() => "?").join(", ");
        sql += ` WHERE package_name IN (${pkPlaceholders})`;
        sqlParams.push(...roleList);
    }

    sql += ` GROUP BY package_name LIMIT ?, ?`;
    sqlParams.push(page, size);

    await pool.query(sql, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));

        // 过滤掉异常包名
        const filteredResult = (result || []).filter((item) => item.package_name && item.package_name !== '');

        const pksToRemove = [
            '1',
            '1222222',
            'applock.calculator.lock.hide.photo.private.vault',
            'com.seamobitech.iam',
            'com.seamobitech.tiktokdownload',
            'tik.tikdownload.nowatermark.video.downloader',
            'tiktok.tiktokdownload.nowatermark.video.downloader',
            'tt.tiktok.downloader.video.saver.nowatermark',
            'video.downloader.insta.videodownloader.ig',
            '包名',
            '包名1',
            'com.tctool.testApp',
            'com.davinci.roku',
            'com.davinci.testApp',
            'com.zhijian.LGTVRemoteTest',
            'com.ZhiJian.LGTVRemote',
            'scan.qr.code.barcode.scaner',
        ];

        // 这里原来错用 .row.pk，应为 .row.package_name
        const filteredData = filteredResult.filter(row => !pksToRemove.includes(row.package_name));

        // 我们需要确保这两个momo业务主包一定出现在结果里
        const momoPkArr = [
            'com.trans.translationAiAndSubtitle',
            'com.TranslationAISubTitle',
            'com.withYou.TestApp',
            'com.finger.withYou'
        ];

        // 结果中可能已经有这俩包了，我们判断没有再添加
        const pkNameSet = new Set(filteredData.map(row => row.package_name));
        momoPkArr.forEach(momoPk => {
            if (!pkNameSet.has(momoPk)) {
                filteredData.push({ package_name: momoPk });
            }
        });

        // 打印结果用于排查
        // console.log("最终包名list：", filteredData.map(e => e.package_name));

        // 统一做映射
        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(filteredData),
            })
        );
    });
});


//获取版本更新列表
router.post("/getAppVersionList", async (req, res) => {
    // 打印进入接口和收到的请求参数

    let user = await utils.getUserRole(req, res);
    let roleList = utils.filterAppVersionListByRoleKey(user.roleKey);
    if (user.roleAdmin || user.hasManage) {
        // 是管理 无条件查询所有
        roleList = [];
    }
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { package_name, status, dateRange } = req.body;


    const MOMO_PKS = [
        'com.trans.translationAiAndSubtitle',
        'com.TranslationAISubTitle',
        'com.withYou.TestApp',
        'com.finger.withYou'
    ];

    // 判断是否momo专用包
    const isMomoPk = package_name && MOMO_PKS.includes(package_name);

    // ---------- 关键改动分支：没传package_name ----------
    if (!package_name) {

        // 组合SQL条件
        let sqlConditions = ["state = 0"];
        const sqlParams = [];
        if (status) {
            sqlConditions.push("status = ?");
            sqlParams.push(status);
        }
        if (dateRange && dateRange.start && dateRange.end) {
            sqlConditions.push("update_time BETWEEN ? AND ?");
            sqlParams.push(dateRange.start, dateRange.end);
        }
        if (roleList.length > 0) {
            let roleListStr = roleList.map((item) => "?").join(", ");
            sqlConditions.push(`package_name IN (${roleListStr})`);
            sqlParams.push(...roleList);
        }
        let conditionSql = "";
        if (sqlConditions.length > 0) {
            conditionSql = ` WHERE ${sqlConditions.join(" AND ")}`;
        }

        let sqlBase = `SELECT * FROM app_version_management${conditionSql}`;
        let sqlCount = `SELECT COUNT(*) AS total_count FROM app_version_management${conditionSql}`;


        // 执行查询
        let momoList = [];
        let baseList = [];
        let momoCountRes = [{ total_count: 0 }];
        let baseCountRes = [{ total_count: 0 }];

        try {
            // 通用promise查询（你们自己封装过的话正常用即可，否则用 util.promisify 下同）
            [momoList, momoCountRes, baseList, baseCountRes] = await Promise.all([
                utils.queryMOMO(sqlBase, sqlParams),
                utils.queryMOMO(sqlCount, sqlParams),
                utils.query(sqlBase, sqlParams),
                utils.query(sqlCount, sqlParams)
            ]);
        } catch (error) {
            console.error("两个库查询报错：", error);
            return res.send(utils.returnData({ code: -1, err: error, req }));
        }


        // 合并列表
        let result = [...(momoList || []), ...(baseList || [])];
        let total = (momoCountRes[0]?.total_count || 0) + (baseCountRes[0]?.total_count || 0);

        // 排序结果
        result.sort((a, b) => new Date(b.update_time) - new Date(a.update_time));


        // 分页
        const pagedResult = result.slice(page, page + size);

        // 后处理
        pagedResult.forEach((element, idx) => {
            const now = Date.now();
            element.is_active = element.effective_timestamp - now < 0 ? 0 : 1;
            element.create_time = moment(element.create_time)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_time = moment(element.update_time)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.effective_datetime = moment(element.effective_datetime)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.version = element.min_version + '-' + element.max_version;

            // 可用于排查数据
            // console.log("处理后数据", idx, element);
        });

        // 返回
        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(pagedResult),
                total: total,
            })
        );
        return; // 注意一定要return
    }

    // ========== 原逻辑，传了package_name查单一库 ==========
    const dbPool = isMomoPk ? poolmomo : pool;
    const dbQuery = isMomoPk ? utils.queryMOMO : utils.query;
    const getListSumFunc = isMomoPk ? utils.getListSumMOMO : utils.getListSum;

    let sqlConditions = ["state = 0"];
    const sqlParams = [];
    if (package_name) {
        sqlConditions.push("package_name = ?");
        sqlParams.push(package_name);
    }
    if (status) {
        sqlConditions.push("status = ?");
        sqlParams.push(status);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        sqlConditions.push("update_time BETWEEN ? AND ?");
        sqlParams.push(dateRange.start, dateRange.end);
    }
    if (roleList.length > 0) {
        let roleListStr = roleList.map((item) => "?").join(", ");
        sqlConditions.push(`package_name IN (${roleListStr})`);
        sqlParams.push(...roleList);
    }

    let sqlBase = `SELECT * FROM app_version_management`;
    let totalBase = "FROM app_version_management";
    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }
    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    // 打印SQL

    let { total } = await getListSumFunc(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_time DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    // 打印最终执行SQL

    await dbPool.query(sqlBase, sqlParams, (err, result) => {
        if (err) {
            console.error("数据库查询报错：", err);
            return res.send(utils.returnData({ code: -1, err, req }));
        }
        result.forEach((element, idx) => {
            const now = Date.now();
            element.is_active = element.effective_timestamp - now < 0 ? 0 : 1;
            element.create_time = moment(element.create_time)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.update_time = moment(element.update_time)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.effective_datetime = moment(element.effective_datetime)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
            element.version = element.min_version + '-' + element.max_version;

            // 可排查
            // console.log("单库数据", idx, element);
        });
        res.send(
            utils.returnData({
                data: utils.mapPackageNamesToShortNames(result),
                total: total,
            })
        );
    });
});



//获取包名列表
router.post("/getAppList", async (req, res) => {
    let user = await utils.getUserRole(req, res);
    let roleList = utils.filterAppVersionListByRoleKey(user.roleKey);
    if (user.roleAdmin || user.hasManage) {
        //是管理 无条件  查询所有
        roleList = [
            'com.seamobitech.roku',
            'com.seamobitech.UniversalTVRemoteControl',
            'com.seamobitech.whatsscanwebclone',
            'com.seamobitech.QRCodeReadAndGenerate',
            'com.seamobitech.BeautyAI',
            'com.seamobitech.HeartPulse',
            'com.seamobitech.QRCodeCreateAndRead',
            'com.trans.translationAiAndSubtitle',
            'com.TranslationAISubTitle',
            'com.withYou.TestApp',
            'com.finger.withYou'
        ];
    }
    let filteredResult = []
    if (roleList.length != 0) {
        roleList.forEach(item => {
            filteredResult.push(
                {
                    package_name: item
                }
            )
        })
    }
    res.send(
        utils.returnData({
            data: utils.mapPackageNamesToShortNames(filteredResult),
        })
    );







});
async function mysqlQueryMOMO(sql, arr) {
    return new Promise((resolve, reject) => {
        poolmomo.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
//保存
router.post('/save', async (req, res) => {
    try {
        const { name } = await utils.getUserInfo(req, res);

        const {
            id,
            package_name,
            min_version,
            max_version,
            update_summary,
            content,
            update_url,
            update_type,
            status,
            effective_timestamp,
            effective_datetime,
            state,
            c_version
        } = req.body;
        console.log(req.body)
        // 基础字段校验
        if (!package_name || !c_version) {
            return res.status(400).json({ code: 400, message: 'package_name和c_version为必填字段' });
        }
        const MOMO_PKS = [
            'com.trans.translationAiAndSubtitle',
            'com.TranslationAISubTitle',
            'com.withYou.TestApp',
            'com.finger.withYou'
        ];

        const isMomoPk = MOMO_PKS.includes(package_name);

        const dbPool = isMomoPk ? poolmomo : pool;
        const dbQuery = isMomoPk ? utils.queryMOMO : utils.query;
        const getListSumFunc = isMomoPk ? utils.getListSumMOMO : utils.getListSum;
        let dbQueryFunc;
        if (
            package_name == 'com.trans.translationAiAndSubtitle' ||
            package_name == 'com.TranslationAISubTitle' || 'com.withYou.TestApp' || 'com.finger.withYou'
        ) {
            dbQueryFunc = mysqlQueryMOMO;
        } else {
            dbQueryFunc = mysqlQuery;
        }

        let result;
        const updateBy = name;
        const currentTime = utils.formatDate(new Date());

        if (id) {
            // 更新操作
            const updateFields = [];
            const params = [];

            // 动态构建更新字段
            const fieldMap = {
                package_name,
                min_version,
                max_version,
                update_summary,
                update_details: content,
                update_url,
                update_type,
                status,
                effective_timestamp,
                effective_datetime,
                state,
                c_version,
                update_by: updateBy,
                update_time: currentTime
            };

            Object.entries(fieldMap).forEach(([key, value]) => {
                if (value !== undefined && key !== 'id') {
                    updateFields.push(`${key} = ?`);
                    params.push(value);
                }
            });

            params.push(id);
            const updateSql = `
                UPDATE app_version_management 
                SET ${updateFields.join(', ')}
                WHERE id = ?
            `;

            result = await dbQueryFunc(updateSql, params);
        } else {
            // 新增操作
            const newId = utils.createId();
            const insertSql = `
                INSERT INTO app_version_management (
                    id, package_name, min_version, max_version,
                    update_summary, update_details, update_url,
                    update_type, status, effective_timestamp,
                    effective_datetime, state, c_version,
                      update_by, create_time, update_time
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;

            const insertParams = [
                newId,
                package_name,
                min_version || null,
                max_version || null,
                update_summary,
                content || null,
                update_url,
                update_type,
                status || 1,  // 默认启用状态
                effective_timestamp,
                effective_datetime,
                state || 0,   // 默认正常状态
                c_version,
                updateBy,     // 更新人
                currentTime,
                currentTime
            ];

            result = await dbQueryFunc(insertSql, insertParams);
            result.insertId = newId;
        }

        res.json({
            code: 200,
            message: '操作成功',
            data: {
                id: id || result.insertId
            }
        });

    } catch (error) {
        console.error('保存失败:', error);
        res.status(500).json({
            code: 500,
            message: '服务器错误',
            error: error.message
        });
    }
});
// 增强版版本标准化函数
function normalizeVersion(version) {
    if (typeof version !== 'string') throw new Error('版本号必须为字符串');

    // 移除可能存在的v前缀
    const cleanVer = version.replace(/^v/i, '');

    // 拆分并验证数字格式，只取最多3个部分
    const parts = cleanVer.split('.').slice(0, 3);
    if (parts.some(p => !/^\d+$/.test(p))) {
        throw new Error(`非法版本号格式: ${version}`);
    }

    // 补零到3位并格式化为可比较字符串（每段补零到6位防止溢出）
    while (parts.length < 3) parts.push('0');
    return parts.map(p => p.padStart(6, '0')).join('.');
}

// 版本比较函数（可直接比较标准化后的字符串）
function compareVersions(a, b) {
    const aParts = a.split('.').map(Number);
    const bParts = b.split('.').map(Number);

    for (let i = 0; i < 3; i++) {
        if (aParts[i] !== bParts[i]) {
            return aParts[i] - bParts[i];
        }
    }
    return 0;
}
router.post('/check-version', async (req, res) => {
    const { package_name, version, language } = req.body;

    const now = Date.now();

    if (!package_name || !version || !language) {
        return res.status(400).json({
            code: 400,
            message: '缺少必要参数：package_name, version, language'
        });
    }
    const MOMO_PKS = [
        'com.trans.translationAiAndSubtitle',
        'com.TranslationAISubTitle',
        'com.withYou.TestApp',
        'com.finger.withYou'
    ];

    const isMomoPk = MOMO_PKS.includes(package_name);

    const dbPool = isMomoPk ? poolmomo : pool;
    const dbQuery = isMomoPk ? utils.queryMOMO : utils.query;
    const getListSumFunc = isMomoPk ? utils.getListSumMOMO : utils.getListSum;
    let dbQueryFunc;
    if (
        package_name == 'com.trans.translationAiAndSubtitle' ||
        package_name == 'com.TranslationAISubTitle' || package_name == 'com.withYou.TestApp' || package_name == 'com.finger.withYou'
    ) {
         
        dbQueryFunc = mysqlQueryMOMO;
    } else {

        dbQueryFunc = mysqlQuery;
    }

    try {
        //调试
        // let test = `SELECT *
        //     FROM app_version_management
        //     WHERE 
        //         package_name = ?
        //         AND state = 0
        //         AND status = 1`

        // let ress = await mysqlQuery(test, [package_name]);

        // console.log('目标时间戳（UTC）:', ress[0].effective_timestamp);
        // // 时间差（毫秒）
        // const timeDifference = ress[0].effective_timestamp - now;

        // // 输出时间差
        // console.log('距目标时间还有（毫秒）:', timeDifference);

        // // 格式化时间差
        // function formatTimeDifference(ms) {
        //     if (ms < 0) {
        //         return "目标时间已经过去，请检查配置";
        //     }

        //     const days = Math.floor(ms / (1000 * 60 * 60 * 24));
        //     const hours = Math.floor((ms % (1000 * 60 * 60 * 24)) / (1000 * 60 * 60));
        //     const minutes = Math.floor((ms % (1000 * 60 * 60)) / (1000 * 60));
        //     const seconds = Math.floor((ms % (1000 * 60)) / 1000);

        //     return `距目标时间还有：${days} 天 ${hours} 小时 ${minutes} 分 ${seconds} 秒`;
        // }
        // // 输出格式化后的时间差
        // console.log(formatTimeDifference(timeDifference));

        // 第一步：基础查询
        const sql = `
            SELECT *
            FROM app_version_management
            WHERE 
                package_name = ?
                AND state = 0
                AND status = 1
                AND CAST(effective_timestamp AS UNSIGNED) < ?
            ORDER BY 
                CAST(SUBSTRING_INDEX(c_version, '.', 1) AS UNSIGNED) DESC,
                CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(c_version, '.', 2), '.', -1) AS UNSIGNED) DESC,
                CAST(SUBSTRING_INDEX(c_version, '.', -1) AS UNSIGNED) DESC
        `;

        const allResults = await dbQueryFunc(sql, [package_name, now]);

        // 第二步：JS端版本校验
        const validResults = allResults.filter(item => {
            try {
                const userVer = normalizeVersion(version);
                const minVer = normalizeVersion(item.min_version);
                const maxVer = normalizeVersion(item.max_version);
                return userVer >= minVer && userVer <= maxVer;
            } catch (e) {
                console.error('版本号格式错误:', e);
                return false;
            }
        });

        // 第三步：处理返回结果
        if (validResults.length > 0) {
            const item = validResults[0];
            try {
                const details = JSON.parse(item.update_details);
                let content = null;

                // 查找用户指定的language
                for (const entry of details) {
                    if (entry && entry[language]) {
                        content = entry[language];
                        break;
                    }
                }

                // 未找到则查找en_US
                if (!content) {
                    for (const entry of details) {
                        if (entry && entry.en_US) {
                            content = entry.en_US;
                            break;
                        }
                    }
                }

                // 仍找不到则返回code=1
                if (!content) {
                    return res.json({
                        code: 1,
                        message: '未找到匹配的语言内容'
                    });
                }

                // 构建响应数据，移除update_details，添加content
                const { id, package_name, update_url, update_type, c_version } = item;
                const data = { id, c_version, package_name, update_url, update_type, content };

                res.json({
                    code: 0,
                    data: data,
                    message: 'success'
                });

            } catch (e) {
                // JSON解析失败或格式错误
                return res.json({
                    code: 1,
                    message: '版本详情解析失败'
                });
            }
        } else {
            // 无符合条件的版本，返回code=1
            res.json({
                code: 1,
                message: '未找到符合条件的版本信息'
            });
        }
    } catch (error) {
        console.error('数据库查询失败:', error);
        res.status(500).json({
            code: 500,
            message: '服务器内部错误',
            error: error.message
        });
    }
});


async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {
            if (error) {
                return reject(error);
            }
            resolve(results);
        });
    });
}
module.exports = router;
